package classmate.dao.impl;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import classmate.dao.IStudentDao;
import classmate.db.DbConnection;
import classmate.po.Room;
import classmate.po.Student;

public class StudentDaoImpl implements IStudentDao{

	@Override
	public void save(Student student) {
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		java.sql.Date sqlDate = new java.sql.Date(student.getBirthday().getTime());
		String sql = "insert into student values(?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, student.getId());
			ps.setString(2, student.getName());
			ps.setDate(3, sqlDate);
			ps.setString(4, student.getPhoneNumber());
			ps.setString(5, student.getEmail());
			ps.setString(6, student.getNativePlace());	
			ps.setString(7, student.getAddress());
			ps.setInt(8, student.getRoomNo());
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
	}
	
	@Override
	public void update(Student student) {
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		java.sql.Date sqlDate = new java.sql.Date(student.getBirthday().getTime());
		String sql = "update student set name = ?,birthday = ?,phonenumber=?,email = ?,nativeplace=?,address=?,room_no = ? where id = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getName());
			ps.setDate(2, sqlDate);
			ps.setString(3, student.getPhoneNumber());
			ps.setString(4, student.getEmail());
			ps.setString(5, student.getNativePlace());	
			ps.setString(6, student.getAddress());
			ps.setInt(7, student.getRoomNo());
			ps.setLong(8, student.getId());
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
		
	}
	
	@Override
	public void delete(long index) {
		Connection conn = DbConnection.getConnection();
		String sql = "delete from student where id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, index);
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(null, ps);
		}
	}
	
	@Override
	public List<Student> get(long index) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "select id,name,birthday,phonenumber,email,nativeplace,address,student.room_no,room_name from student,room where id = ? and student.room_no = room.room_no";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, index);
			rs = ps.executeQuery();
			while (rs.next()){
				long id = rs.getLong(1);
				String name = rs.getString(2);
				Date birthday = rs.getDate(3);
				String phoneNumber = rs.getString(4);
				String email = rs.getString(5);
				String nativePlace = rs.getString(6);
				String address = rs.getString(7);
				int roomNo = rs.getInt(8);
				String roomName = rs.getString(9);
				Room room = new Room(roomNo,roomName);
				Student student = new Student(id, name, birthday, phoneNumber, email, nativePlace, address, room);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;	
	}
	
	@Override
	public List<Student> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "select id,name,birthday,phonenumber,email,nativeplace,address,student.room_no,room_name from student,room where student.room_no = room.room_no";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				Long id = rs.getLong(1);
				String name = rs.getString(2);
				Date birthday = rs.getDate(3);
				String phoneNumber = rs.getString(4);
				String email = rs.getString(5);
				String nativePlace = rs.getString(6);
				String address = rs.getString(7);
				int roomNo = rs.getInt(8);
				String roomName = rs.getString(9);
				Room room = new Room(roomNo,roomName);
				Student student = new Student(id, name, birthday, phoneNumber, email, nativePlace, address, room);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
		
	}
}


